<?php
class Model_Admin_SysUser extends Model{
    public function getInfo($name,$email,$offset,$rows){
        $total= DB::select(array(DB::expr('count(1)'), 'total'))->from('sysuser')->where("FullName",'like','%'.$name.'%')->and_where("email",'like','%'.$email.'%')->execute()->get("total");
        $sql="select t.sysuserid id,t.fullName username,t.email,t.sex,t.age,t.createdon,t.createdby,t.remark
             from sysuser t
             where t.FullName like '%$name%'
             and t.email like '%$email%'
             and t.isdeleted<>'1'
             order by t.CreatedOn desc
             limit $offset,$rows";
         $data=DB::query(Database::SELECT,$sql)->execute()->as_array();
         return array("total"=>$total,"rows"=>$data);
    }
    public  function saveInfo($id,$username,$email,$sex,$age,$remark){
        $total= DB::select(array(DB::expr('count(1)'), 'total'))->from('sysuser')->where("email",'=',$email)->and_where('sysuserid','!=',$id)->execute()->get("total");
        if($total!="0"){
            return json_encode(array("info"=>"对不起，邮箱已经存在！"));
        }
        if($id==""){
            return $this->add($username,$email,$sex,$age,$remark);
        }
        else{
            return $this->alter($id,$username,$email,$sex,$age,$remark);
        }
    }
    private function add($username,$email,$sex,$age,$remark){
        try {
            list($insert_id, $total_rows)= DB::insert('sysuser')->columns(array('fullName','email','sex','age','remark','CreatedBy','CreatedOn'))
                ->values(array($username,$email,$sex,$age,$remark,Session::instance()->get('admin_username',NULL),date("Y-m-d h:i:sa")))->execute();
            Database::instance()->commit();
            return json_encode(array("info"=>"success","count"=>$total_rows,"id"=>$insert_id));
        }
        catch (Exception $e) {
            Database::instance()->rollback();
            return json_encode(array("info"=>"服务器错误，保存失败，请联系管理员或者重试"));
        }
    }
    private function alter($id,$username,$email,$sex,$age,$remark){
        try {
            $total_rows=DB::update('sysuser')->set(array('fullName'=>$username,'email'=>$email,'sex'=>$sex,'age'=>$age,'remark'=>$remark))
                ->where('sysuserid','=',$id)->execute();
            Database::instance()->commit();
            return json_encode(array("info"=>"success","count"=>$total_rows));
        }
        catch (Exception $e) {
            Database::instance()->rollback();
            return json_encode(array("info"=>"服务器错误，保存失败，请联系管理员或者重试"));
        }
    }
    public function  login($email,$pwd){
        $count=DB::select('sysuserid')->from('sysuser')->where('email', '=', $email)->execute()->count();
        if($count==1){
            $sql="select t.sysuserid,t.fullname
             from sysuser t
             where t.email='$email'
             and t.loginpwd=sha1(md5('$pwd'))";
            $query=DB::query(Database::SELECT,$sql)->execute()->as_array();
            $count=count($query);
            if($count==1){
                $arr= array('info'=>'success');
                Session::instance()->set('admin_username',$query[0]["fullname"]);
                Session::instance()->set('admin_userid',$query[0]["sysuserid"]);
                return json_encode($arr);
            }
            else{
                $arr= array('info'=>'failure',"code"=>"pwd");
                return json_encode($arr);
            }

        }
        else{
            $arr= array('info'=>'failure',"code"=>"email");
            return json_encode($arr);
        }
    }
    public function getUserByUsersid($tag,$usersid,$name,$email,$offset,$pageSize){
        if(empty($tag)){
             $sql="select count(1) as total
             from `sysuser` t
             where t.FullName like '%$name%'
             and t.email like '%$email%'
             and exists
             (select 1 from `user_group` g where g.userid=t.sysuserid and g.usersid='$usersid')";

        }
        else{
             $sql="select count(1) as total
             from `sysuser` t
             where t.FullName like '%$name%'
             and t.email like '%$email%'
             and not exists
             (select 1 from `user_group` g where g.userid=t.sysuserid and g.usersid='$usersid')";
        }
       
        $total= DB::query(Database::SELECT,$sql)->execute()->get("total");

        if(empty($tag)){
             $sql="select t.sysuserid id,t.fullName username,t.email,t.sex,t.age,t.createdon,t.createdby,t.remark
             from `sysuser` t
             where t.FullName like '%$name%'
             and t.email like '%$email%'
             and exists
             (select 1 from `user_group` g where g.userid=t.sysuserid and g.usersid='$usersid')
             order by t.CreatedOn desc
             limit $offset,$pageSize";
        }
        else{
            $sql="select t.sysuserid id,t.fullName username,t.email,t.sex,t.age,t.createdon,t.createdby,t.remark
             from `sysuser` t
             where t.FullName like '%$name%'
             and t.email like '%$email%'
             and not exists
             (select 1 from `user_group` g where g.userid=t.sysuserid and g.usersid='$usersid')
             order by t.CreatedOn desc
             limit $offset,$pageSize";
        }
       
        $data=DB::query(Database::SELECT,$sql)->execute()->as_array();

        return array("total"=>$total,"rows"=>$data);
    }
    public function alertPwd($old_pwd,$new_pwd){

        $userid=Session::instance()->get('admin_userid');

        $old_pwd=sha1(md5($old_pwd));

        $sql="select count(1) as total
             from `sysuser` t
             where t.sysuserid='$userid'
             and LoginPwd='$old_pwd'";

        $total= DB::query(Database::SELECT,$sql)->execute()->get("total");

        if($total=="0"){

            return json_encode(array('code'=>"0x00001","info"=>"对不起，旧密码错误")); 
             
        }
        else{

            try {

                $total_rows=DB::update('sysuser')

                ->set(array('LoginPwd'=>sha1(md5($new_pwd))))

                ->where('sysuserid','=',$userid)->execute();

                Database::instance()->commit();

                return json_encode(array("code"=>"0x0000x"));
            }
            catch (Exception $e) {

                Database::instance()->rollback();

                return json_encode(array("info"=>"服务器错误，保存失败，请联系管理员或者重试"));
            }

        }
    }
}